/*
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 */

package com.wttech.dzzf.process.dao;

import com.wtkj.dao.impl.ListResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfwd.*;
import com.wttech.dzzf.process.model.AccountKey;
import com.wttech.dzzf.process.model.CompanyIdKey;
import com.wttech.dzzf.process.model.UserIdKey;
import com.wttech.dzzf.process.model.user.AccountNameKey;
import com.wttech.dzzf.process.model.user.User;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

/**
 * 导入历史数据DAO实现
 *
 * @author MaShibo
 * @date 2017/12/10.
 */
@Repository
public class ImportHisDataDao {

	@Resource(name = "jdbcTemplate")
	private JdbcTemplate testJdbcTemplate;

	@Resource(name = "dzzfJdbcTemplate")
	private JdbcTemplate dzzfJdbcTemplate;


	public Long querySequenceNo(String seqName) {
		final String sql = "SELECT %s.NEXTVAL AS SEQ FROM DUAL ";
		return dzzfJdbcTemplate.queryForObject(String.format(sql, seqName), Long.class);
	}

	public int updateFundAccountNo(String fundAccountId, String fundAccountNo) {
		final String sql = "UPDATE AC_FUNDACCOUNT A SET A.FUNDACCOUNTNO = ? WHERE A.FUNDACCOUNTID=? ";
		return testJdbcTemplate.update(sql, fundAccountNo, fundAccountId);
	}

	@SuppressWarnings("unchecked")
	public List<Account> queryAccountsByFundAccountId(String fundAccountId) {
		final String sql =
				"SELECT A.*, T.TRADENO " +
						"  FROM AC_ACCOUNT A " +
						"  LEFT JOIN AC_ACCOUNTOPT T " +
						"    ON A.ACCOUNTID = T.ACCOUNTID " +
						"   AND T.OPTTYPE = 1010 " +
						" WHERE A.FUNDACCOUNTID = ?";
		return (List<Account>) testJdbcTemplate.query(sql, new ListResultSetExtractor(new Account()), fundAccountId);
	}

	public int updateAccountNoAndClientId(String accountId, String accountNo, String clientId) {
		final String sql = "UPDATE AC_ACCOUNT A SET A.ACCOUNTNO = ?, A.CLIENTID=? WHERE A.ACCOUNTID=? ";
		return testJdbcTemplate.update(sql, accountNo, clientId, accountId);
	}

	public int updateAccountOptNo(String accountId, String accountNo) {
		final String sql = "UPDATE AC_ACCOUNTOPT A SET A.ACCOUNTNO = ? WHERE A.ACCOUNTID = ? ";
		return testJdbcTemplate.update(sql, accountNo, accountId);
	}

	public int updateAccountOptTradeNo(String accountId, String tradeNo){
		final String sql = "UPDATE AC_ACCOUNTOPT A SET A.TRADENO=? WHERE A.ACCOUNTID=? ";
		return testJdbcTemplate.update(sql, tradeNo, accountId);
	}

	public int updateFundAccountIdOfAccount(String accountId, String fundAccountId){
		final String sql = "UPDATE AC_ACCOUNT A SET A.FUNDACCOUNTID=? WHERE A.ACCOUNTID=? ";
		return testJdbcTemplate.update(sql, fundAccountId, accountId);
	}

	@SuppressWarnings("unchecked")
	public List<CardInfo> queryCardInfosByAccountId(String accountId) {
		final String sql = "SELECT * FROM AC_CARDINFO A WHERE A.ACCOUNTID = ? ";
		return (List<CardInfo>) testJdbcTemplate.query(sql, new ListResultSetExtractor(new CardInfo()), accountId);
	}

	@SuppressWarnings("unchecked")
	public VehicleInfo queryVehicleInfoByVheInfo(String vehPlate, Integer vehColor) {
		final String sql =
				"SELECT * " +
						"  FROM (SELECT A.*, ROWNUM AS RM " +
						"          FROM AC_VEHICLEINFO A " +
						"         WHERE A.VEHPLATE = ? " +
						"           AND A.VEHCOLOR = ?) " +
						" WHERE RM = 1";
		List<VehicleInfo> vehicleInfos = (List<VehicleInfo>) testJdbcTemplate.query(sql, new ListResultSetExtractor(new VehicleInfo()), vehPlate, vehColor);
		return CollectionUtils.isEmpty(vehicleInfos) ? null : vehicleInfos.get(0);
	}

	public int queryDzzfVehicleInfoNum(String vehPlate, Integer vehColor) {
		final String sql = "SELECT COUNT(1) FROM AC_VEHICLEINFO A WHERE A.VEHPLATE = ? AND A.VEHCOLOR = ? ";
		return dzzfJdbcTemplate.queryForObject(sql, Integer.class, vehPlate, vehColor);
	}

	public List<AccountKey> queryAccountKeys() {
		final String sql =
				"SELECT A.CERTIFINO, A.CERTIFITYPE " +
						"  FROM AC_FUNDACCOUNT A " +
						" WHERE A.CERTIFINO IS NOT NULL " +
						" GROUP BY A.CERTIFINO, A.CERTIFITYPE " +
						"HAVING COUNT(1) > 1";
		return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(AccountKey.class));
	}

	@SuppressWarnings("unchecked")
	public List<FundAccount> queryFundAccountsByCert(String certifiNo, Integer certifiType) {
		final String sql = "SELECT * FROM AC_FUNDACCOUNT A WHERE A.CERTIFINO = ? AND A.CERTIFITYPE = ? ORDER BY A.OPTON DESC";
		return (List<FundAccount>) testJdbcTemplate.query(sql, new ListResultSetExtractor(new FundAccount()), certifiNo, certifiType);
	}

	public int updateCardInfoAccountId(String finalAccountId, String accountId) {
		final String sql = "UPDATE AC_CARDINFO A SET A.ACCOUNTID=? WHERE A.ACCOUNTID=? ";
		return testJdbcTemplate.update(sql, finalAccountId, accountId);
	}

	public int updateAccountFundAccountId(String finalFundAccountId, String fundAccountId) {
		final String sql = "UPDATE AC_ACCOUNT A SET A.FUNDACCOUNTID=? WHERE A.FUNDACCOUNTID=? ";
		return testJdbcTemplate.update(sql, finalFundAccountId, fundAccountId);
	}

	public int deleteFundAccount(String fundAccountId) {
		final String sql = "DELETE FROM AC_FUNDACCOUNT A WHERE A.FUNDACCOUNTID = ? ";
		return testJdbcTemplate.update(sql, fundAccountId);
	}

	public int deleteAccount(String accountId) {
		final String sql = "DELETE FROM AC_ACCOUNT A WHERE A.ACCOUNTID=? ";
		return testJdbcTemplate.update(sql, accountId);
	}

	public int deleteAccountOpt(String accountId) {
		final String sql = "DELETE FROM AC_ACCOUNTOPT A WHERE A.ACCOUNTID=? ";
		return testJdbcTemplate.update(sql, accountId);
	}

	public List<AccountNameKey> queryAccountNameKeys() {
		final String sql =
				"SELECT A.FUNDACCOUNTNAME, A.MOBILE " +
						"  FROM AC_FUNDACCOUNT A " +
						" WHERE A.CERTIFINO IS NULL " +
						" GROUP BY A.FUNDACCOUNTNAME, A.MOBILE " +
						"HAVING COUNT(1) > 1 ";
		return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(AccountNameKey.class));
	}

	@SuppressWarnings("unchecked")
	public List<FundAccount> queryFundAccountsByName(String fundAccountName, String mobile) {
		final String baseSQL = "SELECT * FROM AC_FUNDACCOUNT A WHERE A.FUNDACCOUNTNAME = ? ";
		StringBuilder sql = new StringBuilder(baseSQL);
		List<Object> params = new ArrayList<>(2);
		params.add(fundAccountName);
		if (StringUtils.isNotBlank(mobile)) {
			sql.append(" AND A.MOBILE = ? ");
			params.add(mobile);
		}
		sql.append(" ORDER BY A.OPTON DESC ");
		return (List<FundAccount>) testJdbcTemplate.query(sql.toString(), new ListResultSetExtractor(new FundAccount()), params.toArray());
	}

	public List<UserIdKey> queryAllUserIdKey() {
		final String sql =
				"SELECT A.USERIDTYPE, A.USERIDNUM " +
						"  FROM KF_USERINFO A " +
						" WHERE A.USERIDTYPE <> 199 " +
						" GROUP BY A.USERIDTYPE, A.USERIDNUM " +
						"HAVING COUNT(1) > 1  ORDER BY USERIDNUM";
		return dzzfJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserIdKey.class));
	}

	@SuppressWarnings("unchecked")
	public List<UserInfo> queryUserInfosByCert(Integer userIdType, String userIdNum) {
		final String sql = "SELECT * FROM KF_USERINFO K WHERE K.USERIDTYPE = ? AND K.USERIDNUM = ?  ORDER BY K.REGISTEREDTIME DESC";
		return (List<UserInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new UserInfo()), userIdType, userIdNum);
	}

	public int updateCardInfoUserId(String userId, String oldUserId) {
		final String sql = "UPDATE AC_CARDINFO A SET A.USERID=? WHERE A.USERID=? ";
		return dzzfJdbcTemplate.update(sql, userId, oldUserId);
	}

	public int updateUserInfoOptUserId(String userId, String oldUserId) {
		final String sql = "UPDATE AC_USERINFOOPT A SET A.USERID=? WHERE A.USERID=? ";
		return dzzfJdbcTemplate.update(sql, userId, oldUserId);
	}

	public int deleteRepeatUserInfo(String oldUserId) {
		final String sql = "DELETE FROM KF_USERINFO A WHERE A.ID=? ";
		return dzzfJdbcTemplate.update(sql, oldUserId);
	}

	public int deleteRepeatCompanyInfo(String oldUserId) {
		final String sql = "DELETE FROM KF_COMPANYINFO A WHERE A.ID=? ";
		return dzzfJdbcTemplate.update(sql, oldUserId);
	}

	public List<CompanyIdKey> queryAllCompanyKeys() {
		final String sql =
				"SELECT A.USERIDTYPE, A.USERIDNUM, A.DEPARTMENT " +
						"  FROM KF_COMPANYINFO A " +
						" WHERE A.USERIDTYPE <> 299 " +
						" GROUP BY A.USERIDTYPE, A.USERIDNUM, A.DEPARTMENT " +
						"HAVING COUNT(1) > 1";
		return dzzfJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(CompanyIdKey.class));
	}

	@SuppressWarnings("unchecked")
	public List<CompanyInfo> queryCompanyInfosByCert(CompanyIdKey companyIdKey) {
		final String sql = "SELECT * FROM KF_COMPANYINFO A WHERE A.USERIDTYPE = ? AND A.USERIDNUM = ? AND A.DEPARTMENT = ? ORDER BY A.REGISTEREDTIME DESC";
		return (List<CompanyInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new CompanyInfo()), companyIdKey.getUserIdType(), companyIdKey.getUserIdNum(), companyIdKey.getDepartment());
	}

	@SuppressWarnings("uncheck")
	public FundAccount queryLastestFundAccount(String certifiNo, Integer certifiType){
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.*,\n" +
						"               J.CHANNELID,\n" +
						"               ROW_NUMBER() OVER(PARTITION BY A.CERTIFINO, A.CERTIFITYPE ORDER BY A.OPTON DESC) AS RM\n" +
						"          FROM AC_FUNDACCOUNT A\n" +
						"          LEFT JOIN JG_ORGANIZATION J\n" +
						"            ON A.CLIENTNO = J.CODE\n" +
						"         WHERE A.CERTIFINO = ?\n" +
						"           AND A.CERTIFITYPE = ?\n" +
						"           AND A.TOPCLASS = 6105)\n" +
						" WHERE RM = 1";
		List<FundAccount> fundAccounts = (List<FundAccount>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new FundAccount()), certifiNo, certifiType);
		return CollectionUtils.isEmpty(fundAccounts) ? null : fundAccounts.get(0);
	}

	public UserInfo queryUserInfoByCert(String certifiNo, Integer certifiType){
		final String sql = "SELECT * FROM KF_USERINFO A WHERE A.CERTIFINO=? AND A.CERTIFITYPE=?";
		List<UserInfo> userInfos = dzzfJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserInfo.class), certifiNo, certifiType);
		return CollectionUtils.isEmpty(userInfos) ? null : userInfos.get(0);
	}

	public CompanyInfo queryCompanyInfoByCert(String certifiNo, Integer certifiType){
		final String sql = "SELECT * FROM KF_COMPANYINFO A WHERE A.CERTIFINO=? AND A.CERTIFITYPE=? AND A.CHANNELID LIKE '61010102002%' " ;
		List<CompanyInfo> companyInfos = dzzfJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(CompanyInfo.class), certifiNo, certifiType);
		return CollectionUtils.isEmpty(companyInfos) ? null : companyInfos.get(0);
	}

	public int updateUserIdOfCardInfo(String userId, String fundAccountId){
		final String sql =
				"UPDATE AC_CARDINFO A\n" +
						"   SET A.USERID = ?\n" +
						" WHERE EXISTS (SELECT 1\n" +
						"          FROM AC_FUNDACCOUNT F\n" +
						"          JOIN AC_ACCOUNT T\n" +
						"            ON F.FUNDACCOUNTID = T.FUNDACCOUNTID\n" +
						"           AND T.ACCOUNTID = A.ACCOUNTID\n" +
						"           AND F.FUNDACCOUNTID = ?)";
		return testJdbcTemplate.update(sql, userId, fundAccountId);
	}

	public User queryPersonUser(Integer userIdType, String userIdnum){
		final String sql =
				"SELECT A.ID,\n" +
						"       1 AS USERTYPE,\n" +
						"       A.USERNAME,\n" +
						"       A.USERIDTYPE,\n" +
						"       A.USERIDNUM,\n" +
						"       A.TEL,\n" +
						"       A.ADDRESS,\n" +
						"       2 AS REGISTEREDTYPE,\n" +
						"       A.CHANNELID,\n" +
						"       TO_CHAR(A.REGISTEREDTIME, 'yyyy-MM-dd') || 'T' ||\n" +
						"       TO_CHAR(A.REGISTEREDTIME, 'hh24:mi:ss') AS REGISTEREDTIME,\n" +
						"       A.STATUS,\n" +
						"       TO_CHAR(A.STATUSCHANGETIME, 'yyyy-MM-dd') || 'T' ||\n" +
						"       TO_CHAR(A.STATUSCHANGETIME, 'hh24:mi:ss') AS STATUSCHANGETIME,\n" +
						"       1 AS OPERATION\n" +
						"  FROM KF_USERINFO A\n" +
						" WHERE A.USERIDTYPE = ?\n" +
						"   AND A.USERIDNUM = ?";
		List<User> users = dzzfJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(User.class), userIdType, userIdnum);
		return CollectionUtils.isEmpty(users) ? null : users.get(0);
	}

	public User queryCompanyUser(Integer userIdType, String userIdNum){
		final String sql =
				"SELECT A.ID,\n" +
						"       2 AS USERTYPE,\n" +
						"       A.USERNAME,\n" +
						"       A.USERIDTYPE,\n" +
						"       A.USERIDNUM,\n" +
						"       A.TEL,\n" +
						"       A.ADDRESS,\n" +
						"       2 AS REGISTEREDTYPE,\n" +
						"       A.CHANNELID,\n" +
						"       TO_CHAR(A.REGISTEREDTIME, 'yyyy-MM-dd') || 'T' ||\n" +
						"       TO_CHAR(A.REGISTEREDTIME, 'hh24:mi:ss') AS REGISTEREDTIME,\n" +
						"       A.STATUS,\n" +
						"       TO_CHAR(A.STATUSCHANGETIME, 'yyyy-MM-dd') || 'T' ||\n" +
						"       TO_CHAR(A.STATUSCHANGETIME, 'hh24:mi:ss') AS STATUSCHANGETIME,\n" +
						"       1 AS OPERATION,\n" +
						"       A.AGENTNAME,\n" +
						"       A.AGENTIDTYPE,\n" +
						"       A.AGENTIDNUM\n" +
						"  FROM KF_COMPANYINFO A\n" +
						" WHERE A.USERIDTYPE = ?\n" +
						"   AND A.USERIDNUM = ?\n" +
						"   AND A.CHANNELID LIKE '%61010102002%'\n" +
						" ORDER BY A.REGISTEREDTIME DESC ";
		List<User> users = dzzfJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(User.class), userIdType, userIdNum);
		return CollectionUtils.isEmpty(users) ? null : users.get(0);
	}



	public static void main(String[] args) {
		final String sql ="SELECT *\n" +
				"  FROM (SELECT A.*,\n" +
				"               ROW_NUMBER() OVER(PARTITION BY A.CERTIFINO, A.CERTIFITYPE ORDER BY A.OPTON DESC) AS RM\n" +
				"          FROM AC_FUNDACCOUNT A\n" +
				"         WHERE A.CERTIFINO = ?\n" +
				"           AND A.CERTIFITYPE = ?\n" +
				"           AND A.TOPCLASS = 6105)\n" +
				" WHERE RM = 1";
		System.out.println(sql);
	}

}
